#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
#include <string.h>
#define PRINT_HELP(binName) printf("USAGE :  %s  <cmd> \n%s", binName, usage_help)

const char *usage_help = "cmd:\n"
                         "1. init       : init database\n"
                         "2. show       : show person table\n"
                         "3. select <id>:  select person info of id\n"
                         "4. del    <id>:  del person  of id\n";

const char *sql_createTable = "drop table if exists Person;"
                              "create table  Person  (id integer primary key  autoincrement, firstName text,secondName text);";

const char *sql_initTable = "insert into Person (id,firstName,secondName) values(101,'Danny','young');"
                            "insert into Person (id,firstName,secondName) values(102,'Christine','Holand');"
                            "insert into Person (id,firstName,secondName) values(103,'Lars','Gordon');"
                            "insert into Person  (id,firstName,secondName) values(104,'Roberto','Robitaille');"
                            "insert into Person (id,firstName,secondName) values(105,'Maria','Papadopoulos');";

const char *sql_show = "select * from Person;";

const char *sql_select_id = "select * from Person where id=%d;";
const char *sql_del_id = "delete from Person where id=%d";

//创建table
int createTable(sqlite3 *db)
{
    char *error = NULL;
    int rc = sqlite3_exec(db, sql_createTable, NULL, NULL, &error);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "open  db error: %s\n", error);
        sqlite3_free(error);
    }
    return rc;
}
//初始化插入数据
int initTable(sqlite3 *db)
{
    char *error = NULL;
    int rc = sqlite3_exec(db, sql_initTable, NULL, NULL, &error);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "create error: %s\n", error);
        sqlite3_free(error);
    }
    return rc;
}
/**
 * @brief 查询回调函数
 *
 * @param param
 * @param f_count 字段个数
 * @param f_valueList 字段值类别
 * @param f_nameList 字段名列表
 * @return int
 */
int callback_select(void *param, int f_count, char **f_valueList, char **f_nameList)
{
    int i;
    for (i = 0; i < f_count; ++i)
        printf("%-12s=%-8s\n", f_nameList[i], f_valueList[i] ? f_valueList[i] : "Null");
    return 0;
}

int delById(sqlite3 *db, int id)
{
    char buf[1024] = {};
    char *error;
    sprintf(buf, sql_del_id, id);
    int rc = sqlite3_exec(db, buf, NULL, NULL, &error);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "delete error:%s\n", error);
        sqlite3_free(error);
    }
    return rc;
}
int selectById(sqlite3 *db, int id)
{
    char buf[1024] = {};
    char *error;
    sprintf(buf, sql_select_id, id);
    int rc = sqlite3_exec(db, buf, callback_select, NULL, &error);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "select error:%s\n", error);
        sqlite3_free(error);
    }
    return rc;
}

int showTable(sqlite3 *db)
{
    char *error;
    int rc = sqlite3_exec(db, sql_show, callback_select, NULL, &error);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "show error: %s", error);
        sqlite3_free(error);
    }
    return rc;
}

int main(int argc, char *argv[])
{

    // printf("argc %d\n", argc);
    // printf("argv 2 %s\n", argv[1]);

    if (argc < 2)
    {
        PRINT_HELP(argv[0]);
        return -1;
    }

    sqlite3 *db;
    int rc = sqlite3_open("./sqliteDemo.db", &db);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "open db fail \n");
        sqlite3_close(db);
        return -1;
    }

    //初始化数据库

    //查询和删除
    if (rc == SQLITE_OK)
    {
        if (0 == strcmp("init", argv[1]))
        {
            rc = createTable(db);
            if (rc == SQLITE_OK)
            {
                rc = initTable(db);
                if (rc == SQLITE_OK)
                    printf("init success\n");
            }
        }
        else if (0 == strcmp("show", argv[1]))
            showTable(db);
        else if (argc == 3)
        {
            if (0 == strcmp("select", argv[1]))
                selectById(db, atoi(argv[2]));
            else if (0 == strcmp("del", argv[1]))
            {
                if(delById(db, atoi(argv[2]))==SQLITE_OK)
                    printf("delete success!\n");
            }
            else
                PRINT_HELP(argv[0]);
        }
        else
        {
            PRINT_HELP(argv[0]);
        }
    }

    sqlite3_close(db);
    return 0;
}